﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using Volo.Abp.DependencyInjection;

namespace Thinker.SignChannel.Excels;

public class ExcelAccessor : ITransientDependency
{
    public void WriteListToExcel<T>(IEnumerable<T> list,
        Stream stream,
        IExcelTranslator translator,
        string sheetName = "Sheet1",
        string? title = null)
    {
        var dictionaries = list.Select(arg => Transform(arg, translator));
        WriteDictionariesToExcel(dictionaries, stream, sheetName, title);
    }

    public void WriteDictionariesToExcel(IEnumerable<IDictionary<string, object?>> dictionaries,
        Stream stream,
        string sheetName = "Sheet1",
        string? title = null)
    {
        title ??= sheetName;

        stream.Seek(0, SeekOrigin.Begin);
        using var excel = new ExcelPackage();
        excel.Workbook.Properties.Title = title;
        var worksheet = excel.Workbook.Worksheets.Add(sheetName);
        worksheet.Cells["A1"].LoadFromDictionaries(dictionaries, opt => opt.PrintHeaders = true);
        worksheet.Cells.AutoFitColumns(0);
        excel.SaveAs(stream);
    }

    public IEnumerable<T> ReadListFromExcel<T>(Stream stream, IExcelTranslator translator)
        where T : new()
    {
        var table = ReadTableFromExcel(stream);
        return table.AsEnumerable().Select(row => ToItem<T>(row, translator));
    }

    private DataTable ReadTableFromExcel(Stream stream)
    {
        using var excel = new ExcelPackage(stream);
        var worksheet = excel.Workbook.Worksheets[0];

        var table = worksheet.Dimension.ToDataTable();
        return table;
    }

    private T ToItem<T>(DataRow row, IExcelTranslator translator) where T : new()
    {
        var item = new T();
        var columns = translator.GetItems(typeof(T)).ToList();
        foreach (var column in columns)
        {
            var propertyName = column.Field;
            var columnName = column.Title;
            var value = row[columnName];
            if (value == null)
            {
                continue;
            }

            var property = typeof(T).GetProperty(propertyName);
            if (property == null)
            {
                continue;
            }

            var propertyType = property.PropertyType;
            value = Convert.ChangeType(value, propertyType);
            property.SetValue(item, value);
        }

        return item;
    }


    private IDictionary<string, object?> Transform<T>(T dto, IExcelTranslator translator)
    {
        return translator.GetItems(typeof(T)).ToDictionary(x => x.Title,
            x => typeof(T).GetProperty(x.Field)?.GetValue(dto));
    }
}